﻿using Com.Lancetinc.Evs.Helpers;
using NLog;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Com.Lancetinc.Evs.Data
{
    public class QueryRepository<TEntity> where TEntity : class
    {
        private static readonly ILogger Logger = LogManager.GetCurrentClassLogger();

        protected readonly PropertyInfo[] Columns;
        protected readonly PropertyInfo[] KeyColumns;
        protected readonly string TableName;
        private string _selectSql;

        public QueryRepository(params string[] keyColumnNames)
        {
            TableName = GetTableName();
            var t = typeof(TEntity);
            Columns = t.GetProperties().Where(x => x.CanRead || x.CanWrite).ToArray();
            KeyColumns = Columns.Where(x => keyColumnNames.Contains(x.Name)).ToArray();
        }

        private string GetTableName()
        {
            var t = typeof(TEntity);
            if (t.GetCustomAttributes(typeof(TableAttribute)).FirstOrDefault() is TableAttribute tblAttr)
                return tblAttr.Name;

            return t.Name;
        }

        protected string AddWhere(DbCommand cmd, SqlCondItem[] condItems)
        {
            if (condItems == null || !condItems.Any())
            {
                return string.Empty;
            }

            var sqlBuilder = new StringBuilder();
            var timesByParamName = new Dictionary<string, int>();
            foreach (var condItem in condItems)
            {
                if (sqlBuilder.Length > 0)
                {
                    sqlBuilder.Append(" and ");
                }

                var normalizedParamName = NormalizeParamName(condItem.Name);
                // 确保参数名称不重复
                if (timesByParamName.ContainsKey(normalizedParamName))
                {
                    timesByParamName[normalizedParamName] = timesByParamName[normalizedParamName] + 1;
                    normalizedParamName += timesByParamName[normalizedParamName];
                }
                else
                {
                    timesByParamName[normalizedParamName] = 1;
                }

                sqlBuilder.Append(NormalizeColumnName(condItem.Name));
                sqlBuilder.Append(" ").Append(condItem.Operator).Append(" ");
                sqlBuilder.Append(normalizedParamName);
                AddDbParameter(cmd, normalizedParamName, NormalizeParamValue(condItem));
            }

            return " where " + sqlBuilder;
        }

        protected SqlCondItem[] ToKeyWhereCondItems(object[] keyValues)
        {
            if (KeyColumns.Length != keyValues.Length)
            {
                throw new ApplicationException("The number of expected parameters is " + KeyColumns.Length +
                                               ", but the number of actual parameters is " + keyValues.Length);
            }

            var condItems = new List<SqlCondItem>();
            for (var i = 0; i < keyValues.Length; i++)
            {
                condItems.Add(new SqlCondItem(KeyColumns[i].Name, keyValues[i]));
            }

            return condItems.ToArray();
        }

        protected int Count(DbCommand cmd, SqlCondItem[] condItems = null)
        {
            var sb = new StringBuilder("select count(*) as cnt from ");
            sb.Append(NormalizeTableName(TableName));
            if (condItems is { Length: > 0 })
            {
                sb.Append(AddWhere(cmd, condItems));
            }

            cmd.CommandText = sb.ToString();
            return Convert.ToInt32(cmd.ExecuteScalar());
        }

        protected object FindMax(DbCommand cmd, string columnName)
        {
            var sb = new StringBuilder("select ifnull(max(").Append(NormalizeColumnName(columnName)).Append("), 0)");
            sb.Append(" from ").Append(NormalizeTableName(TableName));
            cmd.CommandText = sb.ToString();
            return cmd.ExecuteScalar();
        }

        protected TEntity FindByKey(DbCommand cmd, params object[] keyValues)
        {
            return FindFirst(cmd, ToKeyWhereCondItems(keyValues));
        }

        protected TEntity FindFirst(DbCommand cmd, SqlCondItem[] condItems, string orderByClause = null)
        {
            var items = FindList(cmd, condItems, orderByClause, 1);
            return items?.FirstOrDefault();
        }

        protected List<TEntity> FindList(DbCommand cmd, SqlCondItem[] condItems = null,
            string orderByClause = null, int limit = 0, int offset = 0)
        {
            var sb = new StringBuilder(GetSelectSql());
            if (condItems != null && condItems.Any())
            {
                sb.Append(AddWhere(cmd, condItems));
            }

            if (!string.IsNullOrEmpty(orderByClause))
            {
                sb.Append(" order by ").Append(orderByClause);
            }

            if (limit > 0)
            {
                sb.Append(" limit ").Append(limit);
            }

            if (offset > 0)
            {
                sb.Append(" offset  ").Append(offset);
            }

            cmd.CommandText = sb.ToString();

            return FindList(cmd);
        }

        protected List<TEntity> FindList(DbCommand cmd)
        {
            var entities = new List<TEntity>();
            using var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                entities.Add(ReadEntity(reader));
            }

            return entities;
        }

        protected TEntity ReadEntity(DbDataReader reader)
        {
            var entity = Activator.CreateInstance<TEntity>();
            for (var i = 0; i < Columns.Length; i++)
            {
                if (reader.IsDBNull(i))
                    continue;

                var columnValue = TypeConverter.To(reader.GetValue(i), Columns[i].PropertyType);
                if (columnValue != null && Columns[i].PropertyType == typeof(double))
                {
                    columnValue = Math.Round((double)columnValue, 3);
                }

                Columns[i].SetValue(entity, columnValue, null);
            }

            return entity;
        }

        protected DbParameter AddDbParameter(DbCommand cmd, string paraName, object value)
        {
            var dbParam = cmd.CreateParameter();
            dbParam.ParameterName = paraName;
            if (value == null)
            {
                dbParam.Value = DBNull.Value;
            }
            else
            {
                dbParam.Value = value;
            }

            cmd.Parameters.Add(dbParam);
            return dbParam;
        }

        protected string GetColumnName(PropertyInfo propInfo)
        {
            return propInfo.Name;
        }

        protected string NormalizeTableName(string tableName)
        {
            return "[" + tableName + "]";
        }

        protected string NormalizeColumnName(string columnName)
        {
            return "[" + columnName + "]";
        }

        protected string NormalizeParamName(string columnName)
        {
            return "@" + columnName;
        }

        protected object NormalizeParamValue(SqlCondItem condItem)
        {
            if (condItem.Operator == "like")
            {
                return "%" + condItem.Value + "%";
            }

            return condItem.Value;
        }

        protected string GetSelectSql()
        {
            if (_selectSql == null)
            {
                var columnNames = Columns.Select(GetColumnName).ToArray();

                var sb = new StringBuilder("select ");
                sb.Append(string.Join(",", columnNames.Select(NormalizeColumnName)));
                sb.Append(" from ").Append(NormalizeTableName(TableName));
                _selectSql = sb.ToString();
            }

            return _selectSql;
        }

        protected T ExecuteWithoutTrans<T>(Func<DbCommand, T> executor)
        {
            using var conn = SqlDataAccessor.Default.GetConnection();
            using var cmd = conn.CreateCommand();
            var t = executor(cmd);
            LogCommand(cmd);
            return t;
        }

        protected T ExecuteWithTrans<T>(Func<DbCommand, T> executor)
        {
            using var conn = SqlDataAccessor.Default.GetConnection();
            using var trans = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = trans;
            var ret = executor(cmd);
            LogCommand(cmd);
            trans.Commit();
            return ret;
        }

        private void LogCommand(DbCommand cmd)
        {
            var sb = new StringBuilder(cmd.CommandText);
            if (cmd.Parameters.Count > 0)
            {
                sb.Append("\t[");
                for (int i = 0; i < cmd.Parameters.Count; i++)
                {
                    if (i > 0)
                        sb.Append(",");
                    var p = cmd.Parameters[i];
                    sb.Append(p.Value);
                }
                sb.Append("]");
            }
            Logger.Info(sb);
        }
    }
}